package org.lcc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;
import org.lcc.model.entity.ExamRecord;

@Mapper
public interface ExamRecordMapper extends BaseMapper<ExamRecord> {
    @Select("SELECT DISTINCT er.exam_id, tcl.class_id\n" +
            "FROM exam_record er\n" +
            "JOIN student_class_link scl ON er.student_id = scl.school_number\n" +
            "JOIN teacher_class_link tcl ON scl.class_id = tcl.class_id\n" +
            "WHERE er.status = '已批改'\n" +
            "AND NOT EXISTS (\n" +
            "    SELECT 1 FROM score_analysis sa \n" +
            "    WHERE sa.exam_id = er.exam_id \n" +
            "    AND sa.class_id = tcl.class_id\n" +
            ");")
    List<Map<String, Object>> findGradedExamsWithoutAnalysis();
    @Select("SELECT COUNT(*) = 0 as all_graded " +
            "FROM exam_record er " +
            "JOIN student_class_link scl ON er.student_id = scl.school_number " +
            "WHERE er.exam_id = #{examId} " +
            "AND scl.class_id = #{classId} " +
            "AND er.status != '已批改'")
    boolean checkAllExamRecordsGraded(@Param("examId") Integer examId, @Param("classId") Integer classId);

    @Select("SELECT " +
            "AVG(total_score) as average_score, " +
            "MAX(total_score) as highest_score, " +
            "MIN(total_score) as lowest_score, " +
            "JSON_OBJECT(" +
            "'优秀率', CONCAT(ROUND(SUM(CASE WHEN total_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%'), " +
            "'良好率', CONCAT(ROUND(SUM(CASE WHEN total_score >= 80 AND total_score < 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%'), " +
            "'中等率', CONCAT(ROUND(SUM(CASE WHEN total_score >= 70 AND total_score < 80 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%'), " +
            "'及格率', CONCAT(ROUND(SUM(CASE WHEN total_score >= 60 AND total_score < 70 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%'), " +
            "'不及格率', CONCAT(ROUND(SUM(CASE WHEN total_score < 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%')" +
            ") as analysis_data " +
            "FROM exam_record er " +
            "JOIN student_class_link scl ON er.student_id = scl.school_number " +
            "WHERE er.exam_id = #{examId} " +
            "AND scl.class_id = #{classId}")
    Map<String, Object> calculateExamAnalysis(@Param("examId") Integer examId, @Param("classId") Integer classId);

    @Select("SELECT * FROM exam_record WHERE exam_id = #{examId} AND student_id = #{studentId}")
    ExamRecord selectByExamAndStudent(@Param("examId") Long examId,@Param("studentId") String studentId);
}
